\documentclass[a4paper,10pt]{article}
\usepackage{color}
% \usepackage{fancyhdr}
\usepackage[utf8]{inputenc}
\usepackage{enumerate}
\usepackage{latexsym}
\usepackage{amssymb}
\usepackage{ulem}
% \usepackage{ifsym}
\usepackage{graphicx}
% \usepackage{polski}
% \usepackage[polish]{babel}
%\usepackage{a4wide}
% wstawianie obrazkow
%\usepackage{graphicx}
%\usepackage{subfig}

%\usepackage{amsmath}
%\usepackage{amsfonts}
%\usepackage{amsthm}

%\pagestyle{fancy}

%\pagestyle{fancy}

\renewcommand{\headheight}{16pt}

\newcommand{\HRule}{\rule{\linewidth}{0.1mm}}

\begin{document}

%\begin{titlepage}
\begin{center}
%	\textsc{1}\\
%	\textsc{2}\\[4cm]
% 	\HRule\\
%	\HRule \\[2cm]
	\textsc{\Large Distributed Database Systems (WS 11/12) }\\[0.3cm]
	\textsc{\large Assignment 3}\\[1cm]
        Adam Grycner\\
        Szymon Matejczyk\\[1cm]
        \today\\[1cm]
 	\HRule\\
%	\HRule\\[2cm]
	
\end{center}

\section{Exercise 2.3: Optimal automatic horizontal fragmentation}
We have 6 predicates:
\begin{itemize}
 \item $A1$ - $p1$: $division = "G"$ ($DepNo \in [100..250]$)
 \item $A2$ - $p2$: $division = "T"$ ($DepNo \in [251..400]$)
 \item $A3$ - $p3$: $division = "R"$ ($DepNo \in [401..499]$)
 \item $A4$ - $p4$: $DepNo \in [100..150]$
 \item $A5$ - $p5$: $DepNo \in [151..299]$
 \item $A6$ - $p6$: $DepNo \in [300..499]$
\end{itemize}
That gives us 5 non-contradictory minterms:
\begin{itemize}
 \item \sout{$p1 \wedge p4$}
 \item $p1 \wedge p5$
 \item $p2 \wedge p5$
 \item $p2 \wedge p6$
 \item $p3 \wedge p6$
\end{itemize}
We remove first minterm, because it contains non-relevant predicate ($p4$).\\
So we can obtain minimal and complete fragmentation using minterms $p1 \wedge p5$ and $p3 \wedge p6$.\\
%TODO: write something about selectivity?
\begin{itemize}
 \item $F1$: $[100..299]$
 \item $F1$: $[300..499]$
\end{itemize}
%TODO: I completely do not understant this excercise


\section{Exercise 2.4: Derived horizontal fragmentation}
Fragmentation:
\begin{itemize}
 \item $PARTS \ltimes \sigma_{code = 'internal'}(SUPPLIER)$
 \item $PARTS \ltimes \sigma_{code \neq 'internal'}(SUPPLIER)$
\end{itemize}
Correctness rules are met because:
\begin{itemize}
 \item Completeness - supNo is not null nor value, which is not placed in SUPPLIER
 \item Disjointness - relation SUPPLIER is divided separately (two nonoverlapping conditions) - semijoins of PARTS relation with these fragments is disjoint
 \item Reconstruction - primary key remain unique and foreign key is preserved
\end{itemize}
``Join graph'':
\begin{center}
  \begin{figure}[ht]
    \label{F:plot2}
      \includegraphics[width=0.5\textheight]{graph.png}
  \end{figure}
\end{center}
The graph has high ``goodness'', because every fragment is connected only with one connection with other fragment. It's because supplier can only be internal or external. Not both.

\section{Exercise 2.5: Hybrid fragmentation}
\begin{enumerate}
 \item Part1 = $\pi_{partNo, PartName}(PARTS)$: Vertical Fragmentation
 \item Part2 = $\pi_{partNo, supNo, price}(PARTS)$: Vertical Fragmentation
 \begin{enumerate}
  \item Part2.1 = $\sigma_{price \leq 10 EUR}(Part2)$: Horizontal fragmentation
  \item Part2.2 = $\sigma_{price > 10 EUR}(Part2)$: Horizontal fragmentation
  \begin{enumerate}
   \item Part2.2.1 = $Part2.2 \ltimes \sigma_{code = 'internal'}(SUPPLIER)$: Horizontal fragmentation (Derived?)
   \item Part2.2.2 = $Part2.2 \ltimes \sigma_{code \neq 'internal'}(SUPPLIER)$: Horizontal fragmentation (Derived?)
  \end{enumerate}

 \end{enumerate}

\end{enumerate}

\section{Exercise 2.6: Horizontal fragmentation}
\begin{enumerate}[(a)]
 \item 
 $ 
 \pi_{ ( EName, ETitle ) } \sigma_{Salary.Salary >= 65000} $
 $   \sigma_{(Projects.Location \ != \ 'Paris' and Project.Budget >= 100000)} $
  \[ 
 \ \ \ \ ( Employees \Join_{EID = ENO} Assignment) \Join_{Projects.PNO = Assignment.PNO} Projects  \]
   \[ \Join_{Employees.Title = Salary.Title} Salary  \]
 \item The fragmentation is defined by predicates(and their negatives):
   \begin{itemize}
     \item $p_{1} = Salary.Salary >= 65000 $
     \item $p_{2} = Projects.Location \ != \ 'Paris' $
     \item $p_{3} = Project.Budget >= 100000 $
   \end{itemize}
   We obtain same fragmentation using only $p_{2}$ and $p_{3}$, so the fragmentation is not minimal.
 \item Derived fragmentation
   \begin{itemize}
     \item Assignment
     \begin{enumerate}
        \item $Assignment_{1} = \sigma_{ENo = 'E1'\  or\  ENo = 'E3'} Assignment$
        \item $Assignment_{2} = \sigma_{ENo = 'E2'\  or \ ENo = 'E5'} Assignment$
        \item $Assignment_{3} = \sigma_{ENo = 'E4' \ and \  PNo = 'P1'} Assignment$
        \item $Assignment_{4} = \sigma_{ENo = 'E4'\  and \  PNo = 'P3'} Assignment$
     \end{enumerate}
     \item Employees
     \begin{enumerate}
       \item $Employees_{1} =  \sigma_{ENo = 'E1' \ or \ ENo = 'E3'} Employees$
       \item $Employees_{2} =  \sigma_{ENo = 'E2' \ or \ ENo = 'E5'} Employees$
       \item $Employees_{3} = \sigma_{ENo = 'E4'} Employees$
     \end{enumerate}
   \end{itemize}
 \item Join graph (below)
    \begin{center}
      \begin{figure}[ht]
        \label{F:plot2}
          \includegraphics[width=0.5\textheight]{joingraph26.png}
      \end{figure}
    \end{center}
 \item If the query load changes and requires employees earning 60000 or more, then the derived schema is not optional as 'Ann Joy' is in different fragment than rest query (a) results.

 
   


\end{enumerate}

\end{document}
